{
 "metadata": {
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "orig_nbformat": 4,
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3.7.3 32-bit"
  },
  "interpreter": {
   "hash": "31f2aee4e71d21fbe5cf8b01ff0e069b9275f58929596ceb00d14d90e3e16cd6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "source": [
    "# 合并一个excel文件中多个工作表"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import xlrd, xlsxwriter, os, datetime\n",
    "\n",
    "r_filename = 'test.xlsx'\n",
    "\n",
    "fname, exname = os.path.splitext(r_filename)    # 拆分文件名和扩展名\n",
    "\n",
    "w_filename = fname + datetime.datetime.now().strftime('-%Y%m%d %H%M%S') + exname    # 生成写文件我\n",
    "\n",
    "r_wb = xlrd.open_workbook(r_filename)    # 打开读文件\n",
    "\n",
    "w_wb = xlsxwriter.Workbook(w_filename)    # 创建写文件\n",
    "\n",
    "w_ws = w_wb.add_worksheet()    # 添加工作表\n",
    "\n",
    "w_rows = 0    # 写入的最大行\n",
    "\n",
    "for r_ws in r_wb.sheets():    # 循环读取现有工作表\n",
    "\n",
    "    for i in range(r_ws.nrows):    # 循环读取当前工作表的行\n",
    "        w_ws.write(w_rows, 0, r_ws.name)    # 在每行第0列写上读取的工作表名\n",
    "\n",
    "        row_value = r_ws.row_values(i)    # 读取整行\n",
    "\n",
    "        w_ws.write_row(w_rows, 1, row_value)    # 从最大行第1列开始写入\n",
    "\n",
    "        w_rows += 1    # 写入文件的行数+1\n",
    "\n",
    "w_wb.close()    # 关闭文件\n",
    "\n",
    "\n"
   ]
  }
 ]
}